package DB_Layer;

import Model_Layer.*;

import java.sql.*;
import java.util.*;

public class Db_Invoice implements IFDB_Invoice{
	private Connection con;
	/** Creates a new instance of Db */
	public Db_Invoice()
	{
		con = DbConnection.getInstance().getDBcon();
	}
	
	
	public int delete(int invID)
	{
		int rc=-1;
		
		String query = "DELETE FROM Invoice WHERE InvID = " +
				invID;
				System.out.println(query);
		try{
			Statement stmt = con.createStatement();
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception ex)
		{
			System.out.println("Delete exceptiopn in Invoice db: "+ex);
		}
		return(rc);
	}
	
	
	//private methods
	//miscWhere is used when we want to select more the one Invoice
	private ArrayList<Invoice> miscWhere(String wClause, boolean retriveAssociation)
	{
		ResultSet results;
		ArrayList<Invoice> list = new ArrayList<Invoice>();
		
		String query = buildQuery(wClause);
		
			try{
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				results = stmt.executeQuery(query);
				
				
				while (results.next())
				{
					Invoice invObj = new Invoice();
					invObj = buildInvoice(results);
						list.add(invObj);
				}
				stmt.close();
			}
			catch(Exception e)
			{
				System.out.println("Query exception - select: "+e);
				e.printStackTrace();
			}
			return list;
		
	}
	
	//Singelwhere is used when we only select one Invoice
	private Invoice singleWhere(String wClause, boolean retriveAssociation)
	{
		ResultSet results;
		Invoice invObj = new Invoice();
		
			String query = buildQuery(wClause);
				System.out.println(query);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next())
			{
				invObj = buildInvoice(results);
				stmt.close();
			}
			else
			{
				invObj = null;
			}
		}
		catch(Exception e)
		{
			System.out.println("Query exception: "+e);
		}
		return invObj;
	}
	
	//Method to build the query
	private String buildQuery(String wClause)
	{
		String query = "SELECT InvID, InvoiceNo, PaymentDate, Amount FROM Invoice";
		
		if(wClause.length()>0)
			query = query + "WHERE "+ wClause;
		
		return query;
	}
	
	//method to build an Invoice object
	private Invoice buildInvoice(ResultSet results)
	{
		Invoice invObj = new Invoice();
		try{
			invObj.setInvID(results.getInt("InvID"));
			invObj.setInvoiceNo(results.getString("InvoiceNo"));
			invObj.setPaymentDate(results.getString("PaymentData"));
			invObj.setAmount(results.getInt("Amount"));
		}
		catch(Exception e)
		{
			System.out.println("Error in building the Invocie object");
			
		}
		return invObj;
	}

	//Implements the methods from the Interface
	//Get all Invoice's
	public ArrayList<Invoice> getAllInvoice(boolean retriveAssociation)
	{
		return miscWhere("", retriveAssociation);
	}

	//get one Invoice having the ID
	public Invoice findInvoiceID(int ID, boolean retriveAssociation)
	{
		String wClause = " InvID = '" + ID + "'";
		return singleWhere(wClause, retriveAssociation);
	}

	//Get one Invoice having the InvoiceNo
	public Invoice findInvoiceNo(int no, boolean retriveAssociation)
	{
		String wClause = "InvoiceNo = '" + no + "'";
		return singleWhere(wClause,retriveAssociation);
	}

	//Insert a new Invoice
	public int insertInvoice(Invoice inv) throws Exception
	{
		int nextID = GetMaxID.getMaxID("Select max(InvID) from Invoice");
		int nextInvoiceNo = GetMaxID.getMaxID("Select max(InvoiceNo) from Invoice");
		nextInvoiceNo = nextInvoiceNo + 1;
		nextID = nextID + 1;
		System.out.println("Next InvID = " + nextID);
		System.out.println("Next InvoiceNo = " + nextInvoiceNo);
		
		
		int rc = -1;
		String query = "INSERT INTO Invoice(InvID, InvoiceNo, PaymentDate, Amount) VALUES("+
				nextID + "," +
				nextInvoiceNo + ",'" +
				inv.getPaymentDate() + "'," +
				inv.getAmount() + ");";
		
		System.out.println("Insert : " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(SQLException ex)
		{
			System.out.println("Invoice ikke oprettet");
			throw new Exception ("Invoice is not inserted correct");
		}
		return(rc);
	}


	public int updateInvoice(Invoice inv) 
	{
		Invoice invObj = inv;
		int rc = -1;
		
		String query = "UPDATE Invoice Set "+
				"PaymentDate = '"+ invObj.getPaymentDate() +"', " +
				"Amount = "+ invObj.getAmount() +
				" WHERE InvID = " + invObj.getInvID();
		System.out.println("Update query: " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			
			stmt.close();
		}
		catch(Exception ex) {
			System.out.println("Update exception in Invoice db: "+ex);
		}
		return(rc);
	}

}
